package com.scuec.tourismwebsite.mapper;

import com.scuec.tourismwebsite.entity.Post;
import com.scuec.tourismwebsite.vo.UserPost;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PostMapper {
    @Select("SELECT p.post_id,p.city, p.title, p.content, p.category, p.post_date, p.comments_count, p.likes_count,p.image_1,p.image_2,p.image_3,u.avatar, u.nickname " +
            "FROM post p " +
            "INNER JOIN user u ON p.user_id = u.user_id " +
            "ORDER BY p.post_date DESC")
    List<UserPost> findPostAll();

    @Select("SELECT p.post_id, p.title, p.content, p.category, p.post_date, p.comments_count, p.likes_count, u.nickname " +
            "FROM post p " +
            "INNER JOIN user u ON p.user_id = u.user_id " +
            "WHERE\n" +
            "    u.nickname LIKE CONCAT('%', #{nickname}, '%')\n"+
            "ORDER BY p.post_date DESC")
    List<UserPost> findPostByNickname(String nickname);

    @Delete("DELETE FROM post WHERE post_id =#{post_id}")
    Integer deletePostById(Integer post_id);

    @Select("SELECT p.post_id, p.title, p.content, p.category, p.collect_counts, p.post_date, p.comments_count, p.likes_count, p.image_1, p.image_2, p.image_3, u.avatar, u.nickname, u.user_id " +
            "FROM post p " +
            "INNER JOIN user u ON p.user_id = u.user_id " +
            "WHERE p.post_id = #{post_id} " +
            "ORDER BY p.post_date DESC")
    UserPost findPostById(@Param("post_id") Integer postId);

    @Update("UPDATE post SET likes_count = #{likes_count}, comments_count = #{comments_count}, collect_counts = #{collect_counts} where post_id=#{post_id}")
    Integer updatePostCount(@Param("post_id") Integer post_id, @Param("likes_count") Integer likes_count, @Param("comments_count") Integer comments_count, @Param("collect_counts") Integer collect_counts);

    @Select("SELECT p.post_id, p.title, p.content, p.category, p.post_date, p.comments_count, p.likes_count, " +
            "u.nickname, u.avatar " +
            "FROM post p " +
            "INNER JOIN user u ON p.user_id = u.user_id " +
            "WHERE p.user_id = #{user_id} " +
            "ORDER BY p.post_date DESC")
    List<Object[]> findPostByUserId(Integer user_id);

    @Select("SELECT p.post_id,p.city, p.title, p.content, p.category, p.post_date, p.comments_count, p.likes_count,p.image_1,p.image_2,p.image_3,u.avatar, u.nickname " +
            "FROM post p " +
            "INNER JOIN user u ON p.user_id = u.user_id " +
            "WHERE category=#{category}"+
            "ORDER BY p.post_date DESC" )
    List<UserPost> findUserPostByCategory(String category);

    @Insert("INSERT INTO post (post_id, user_id, title, content, category, post_date, create_date, comments_count, likes_count, image_1, image_2, image_3, collect_counts, city) " +
            "VALUES (#{post_id}, #{user_id}, #{title}, #{content}, #{category}, NOW(), NOW(), COALESCE(#{comments_count}, 0), COALESCE(#{likes_count}, 0), #{image_1}, #{image_2}, #{image_3}, COALESCE(#{collect_counts}, 0), #{city})")
    Integer insertPost(Post post);
}


